package com.xiaoshuidi.cloud.module.report.service.ajreport;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.IdcardUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.github.xiaoymin.knife4j.core.util.StrUtil;
import com.google.common.collect.Lists;
import com.xiaoshuidi.cloud.framework.common.exception.ServiceException;
import com.xiaoshuidi.cloud.framework.common.pojo.PageResult;
import com.xiaoshuidi.cloud.framework.oss.core.OosFileService;
import com.xiaoshuidi.cloud.framework.security.core.LoginUser;
import com.xiaoshuidi.cloud.framework.security.core.util.SecurityFrameworkUtils;
import com.xiaoshuidi.cloud.module.report.constant.ContractNodeEnum;
import com.xiaoshuidi.cloud.module.report.constant.ContractStatusEnum;
import com.xiaoshuidi.cloud.module.report.constant.CustomTypeEnum;
import com.xiaoshuidi.cloud.module.report.constant.PayWayEnum;
import com.xiaoshuidi.cloud.module.report.controller.ajreport.vo.*;
import com.xiaoshuidi.cloud.module.report.controller.finance.vo.ExportRespVO;
import com.xiaoshuidi.cloud.module.report.convert.ajreport.LedgerExportConvert;
import com.xiaoshuidi.cloud.module.report.convert.ajreport.LedgerReportConvert;
import com.xiaoshuidi.cloud.module.report.dal.DO.ContractRoommateDO;
import com.xiaoshuidi.cloud.module.report.dal.DO.report.ReportLedgerInfoDO;
import com.xiaoshuidi.cloud.module.report.dal.mapper.ContractRoommateMapper;
import com.xiaoshuidi.cloud.module.report.dal.mapper.report.ReportLedgerInfoMapper;
import com.xiaoshuidi.cloud.module.report.enums.ErrorCodeConstants;
import com.xiaoshuidi.cloud.module.report.framework.security.config.ConfigValues;
import com.xiaoshuidi.cloud.module.report.service.ajreport.dto.ReportLedgerInfoDto;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;

import java.io.File;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;


@Service
@RequiredArgsConstructor
@Slf4j
public class LedgerReportServiceImpl implements LedgerReportService {

    private final ReportLedgerInfoMapper reportLedgerInfoMapper;
    private final ContractRoommateMapper contractRoommateMapper;
    private final OosFileService oosFileService;
    private final ConfigValues configValues;

    @Override
    public PageResult<RentingLedgerRespVO> rentingLedgerPage(LedgerReportReqVO reqVO) {
        LoginUser loginUser = SecurityFrameworkUtils.getLoginUser();
        if(loginUser==null){
            throw new ServiceException(ErrorCodeConstants.USER_NOT_LOGIN);
        }
        Page<ReportLedgerInfoDO> page = new Page<>(reqVO.getPageNo(), reqVO.getPageSize());
        LambdaQueryWrapper<ReportLedgerInfoDO> queryWrapper = getQueryWrapper(reqVO);
        queryWrapper.in(ReportLedgerInfoDO::getContractStatus, Lists.newArrayList(ContractStatusEnum.ACTIVE.getStatus(), ContractStatusEnum.CHECK_IN.getStatus()));
        queryWrapper.ne(ReportLedgerInfoDO::getContractNode, ContractNodeEnum.RENTING_OUT.getNode());
        queryWrapper.eq(ReportLedgerInfoDO::getTenantId,loginUser.getTenantId());
        //开始时间处于今日或之前的即是在租台账
        queryWrapper.le(ReportLedgerInfoDO::getStartTime, LocalDate.now());
//        queryWrapper.orderByDesc(ReportLedgerInfoDO::getHouseResource);
        queryWrapper.last("ORDER BY SUBSTRING_INDEX(house_resource,'栋',-1) ASC");
        log.info("=====租户id:{}",loginUser.getTenantId());
        page = reportLedgerInfoMapper.selectPage(page, queryWrapper);
        if(page==null|| CollectionUtils.isEmpty(page.getRecords())){
            return new PageResult<>(null,0L);
        }
        PageResult<ReportLedgerInfoDO> pageResult = new PageResult<>(page.getRecords(), page.getTotal());
        PageResult<ReportLedgerInfoDto> dtoResult = LedgerReportConvert.INSTANCE.convert(pageResult);
        PageResult<RentingLedgerRespVO> result = LedgerReportConvert.INSTANCE.convertRentingLedgerPage(dtoResult);
        result.getList().forEach(e -> {
            Integer age = getAge(e.getCustomerIdNumber());
            e.setAge(age);
            //填充同住人信息
            List<ContractRoommateDO> contractRoommateDOS = contractRoommateMapper.selectByRoomContractId(e.getContractId());
            e.setRoommateList(LedgerReportConvert.INSTANCE.convertRoommateList(contractRoommateDOS));
        });
        return result;
    }

    @Override
    public PageResult<RenewalLedgerRespVO> renewalLedgerPage(RenewalLedgerReportReqVO reqVO) {
        LoginUser loginUser = SecurityFrameworkUtils.getLoginUser();
        if(loginUser==null){
            throw new ServiceException(ErrorCodeConstants.USER_NOT_LOGIN);
        }
        Page<ReportLedgerInfoDO> page = new Page<>(reqVO.getPageNo(), reqVO.getPageSize());
        LambdaQueryWrapper<ReportLedgerInfoDO> queryWrapper = getRenewalQueryWrapper(reqVO);
        queryWrapper.in(ReportLedgerInfoDO::getContractStatus, Lists.newArrayList(ContractStatusEnum.ACTIVE.getStatus(), ContractStatusEnum.CHECK_IN.getStatus()));
        queryWrapper.eq(ReportLedgerInfoDO::getContractNode, ContractNodeEnum.RENEWAL.getNode());
        queryWrapper.eq(ReportLedgerInfoDO::getTenantId,loginUser.getTenantId());
//        queryWrapper.orderByDesc(ReportLedgerInfoDO::getNewContractDate);
        queryWrapper.last("ORDER BY SUBSTRING_INDEX(house_resource,'栋',-1) ASC");
        page = reportLedgerInfoMapper.selectPage(page, queryWrapper);
        if(page==null|| CollectionUtils.isEmpty(page.getRecords())){
            return new PageResult<>(null,0L);
        }
        PageResult<ReportLedgerInfoDO> pageResult = new PageResult<>(page.getRecords(), page.getTotal());
        PageResult<ReportLedgerInfoDto> dtoResult = LedgerReportConvert.INSTANCE.convert(pageResult);
        PageResult<RenewalLedgerRespVO> result = LedgerReportConvert.INSTANCE.convertRenewalLedgerPage(dtoResult);
        result.getList().forEach(e -> {
            //填充同住人信息
            List<ContractRoommateDO> contractRoommateDOS = contractRoommateMapper.selectByRoomContractId(e.getContractId());
            e.setRoommateList(LedgerReportConvert.INSTANCE.convertRoommateList(contractRoommateDOS));
            e.setOldContractDate(e.getOldStartTime() + "~" + e.getOldEndTime());
        });
        return result;
    }

    @Override
    public PageResult<ExchangeLedgerRespVO> exchangeLedgerPage(LedgerReportReqVO reqVO) {
        LoginUser loginUser = SecurityFrameworkUtils.getLoginUser();
        if(loginUser==null){
            throw new ServiceException(ErrorCodeConstants.USER_NOT_LOGIN);
        }
        Page<ReportLedgerInfoDO> page = new Page<>(reqVO.getPageNo(), reqVO.getPageSize());
        LambdaQueryWrapper<ReportLedgerInfoDO> queryWrapper = getQueryWrapper(reqVO);
        queryWrapper.in(ReportLedgerInfoDO::getContractStatus, Lists.newArrayList(ContractStatusEnum.ACTIVE.getStatus(), ContractStatusEnum.CHECK_IN.getStatus()));
        queryWrapper.eq(ReportLedgerInfoDO::getContractNode, ContractNodeEnum.EXCHANGE.getNode());
        queryWrapper.eq(ReportLedgerInfoDO::getTenantId,loginUser.getTenantId());
//        queryWrapper.orderByDesc(ReportLedgerInfoDO::getUpdateTime);
        queryWrapper.last("ORDER BY SUBSTRING_INDEX(house_resource,'栋',-1) ASC");
        page = reportLedgerInfoMapper.selectPage(page, queryWrapper);
        if(page==null|| CollectionUtils.isEmpty(page.getRecords())){
            return new PageResult<>(null,0L);
        }
        PageResult<ReportLedgerInfoDO> pageResult = new PageResult<>(page.getRecords(), page.getTotal());
        PageResult<ReportLedgerInfoDto> dtoResult = LedgerReportConvert.INSTANCE.convert(pageResult);
        PageResult<ExchangeLedgerRespVO> result = LedgerReportConvert.INSTANCE.convertExchangeLedgerPage(dtoResult);
        result.getList().forEach(e -> {
            Integer age = getAge(e.getCustomerIdNumber());
            e.setAge(age);
            e.setPayWay(PayWayEnum.getValueByMonth(e.getPayMethodF()));
        });
        return result;
    }


    @Override
    public PageResult<RentingOutLedgerRespVO> rentingOutLedgerPage(LedgerReportReqVO reqVO) {
        LoginUser loginUser = SecurityFrameworkUtils.getLoginUser();
        if(loginUser==null){
            throw new ServiceException(ErrorCodeConstants.USER_NOT_LOGIN);
        }
        Page<ReportLedgerInfoDO> page = new Page<>(reqVO.getPageNo(), reqVO.getPageSize());
        LambdaQueryWrapper<ReportLedgerInfoDO> queryWrapper = getQueryWrapper(reqVO);
        queryWrapper.in(ReportLedgerInfoDO::getContractStatus, Lists.newArrayList(ContractStatusEnum.NORMAL_END.getStatus(), ContractStatusEnum.ABNORMAL_END.getStatus()));
        queryWrapper.eq(ReportLedgerInfoDO::getContractNode, ContractNodeEnum.RENTING_OUT.getNode());
//        queryWrapper.isNull(ReportLedgerInfoDO::getFromId);
        //退租过滤存在续租的合同
        queryWrapper.notExists("SELECT 1 FROM contract_room WHERE report_ledger_info.contract_id = from_id AND deleted = 0 AND  contract_status IN ( 'active', 'check_in', 'normal_end','abnormal_end' )");
        queryWrapper.eq(ReportLedgerInfoDO::getTenantId,loginUser.getTenantId());
//        //门店  退租时间  id
//        queryWrapper.orderByDesc(ReportLedgerInfoDO::getApartmentId,ReportLedgerInfoDO::getEvictionTime,ReportLedgerInfoDO::getId);
        queryWrapper.last("ORDER BY SUBSTRING_INDEX(house_resource,'栋',-1) ASC");
        page = reportLedgerInfoMapper.selectPage(page, queryWrapper);
        if(page==null|| CollectionUtils.isEmpty(page.getRecords())){
            return new PageResult<>(null,0L);
        }
        PageResult<ReportLedgerInfoDO> pageResult = new PageResult<>(page.getRecords(), page.getTotal());
        PageResult<ReportLedgerInfoDto> dtoResult = LedgerReportConvert.INSTANCE.convert(pageResult);
        PageResult<RentingOutLedgerRespVO> result = LedgerReportConvert.INSTANCE.convertRentingoutLedgerPage(dtoResult);
        result.getList().forEach(e -> {
            Integer age = getAge(e.getCustomerIdNumber());
            e.setAge(age);
            //填充同住人信息
            List<ContractRoommateDO> contractRoommateDOS = contractRoommateMapper.selectByRoomContractId(e.getContractId());
            e.setRoommateList(LedgerReportConvert.INSTANCE.convertRoommateList(contractRoommateDOS));
        });
        return result;
    }

    private  Integer getAge(String idNumber) {
        String customerIdNumber = StrUtil.isNotBlank(idNumber)? idNumber.replaceAll("\\s", ""):null;
        Integer age = null;
        //年龄计算
        if(StrUtil.isNotBlank(customerIdNumber)){
            try {
                age = IdcardUtil.getAgeByIdCard(customerIdNumber);
            } catch (Exception ex) {
            }
        }
        return age;
    }

    @Override
    public ExportRespVO rentingLedgerExport(LedgerReportReqVO req) {
        LoginUser loginUser = SecurityFrameworkUtils.getLoginUser();
        if(loginUser==null){
            throw new ServiceException(ErrorCodeConstants.USER_NOT_LOGIN);
        }
        req.setTenantId(loginUser.getTenantId());
        ArrayList<String> statusList = Lists.newArrayList(ContractStatusEnum.ACTIVE.getStatus(),ContractStatusEnum.CHECK_IN.getStatus());
        ArrayList<String> nodeList = Lists.newArrayList(ContractNodeEnum.NEW.getNode(), ContractNodeEnum.RENEWAL.getNode(), ContractNodeEnum.EXCHANGE.getNode());
        req.setIsRentalLedger("Y");
        long count = reportLedgerInfoMapper.countLedgerForExport(req, statusList, nodeList);
        if (count <= 0) {
            throw new ServiceException(ErrorCodeConstants.RENTING_LEDGER_NOT_EXISTS);
        }
        int pageSize = 2000;
        int totalPage = (int) count / pageSize;
        if (count % pageSize != 0) {
            totalPage++;
        }

        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmss");
        String format = sdf.format(new Date());
        String fileName = format + "在租台账.xlsx";

        String filePath = configValues.getNfsPath() + File.separator + fileName;
        ExcelWriter excelWriter = EasyExcel.write(filePath, RentingLedgerExportVO.class).build();
        WriteSheet sheet = EasyExcel.writerSheet("sheet1").build();
        for (int i = 1; i <= totalPage; i++) {
            List<ReportLedgerInfoDO> infoDOList = reportLedgerInfoMapper.selectLedgerListForExport(req, statusList, nodeList, (i - 1) * pageSize, pageSize);
            List<RentingLedgerExportVO> list = new ArrayList<>();
            List<Long> contractIds = infoDOList.stream().map(ReportLedgerInfoDO::getContractId).collect(Collectors.toList());
            List<ContractRoommateDO> roommateDOS = contractRoommateMapper.selectByRoomContractIds(contractIds);
            //@date 2023/11/23 shen update
            infoDOList.stream().forEach(f->{
                //当前合同 下同住人
                List<ContractRoommateDO> collect =
                        roommateDOS.stream().filter(rs -> f.getContractId().equals(rs.getRoomContractId())).collect(Collectors.toList());
                if(CollectionUtil.isNotEmpty(collect)){
                    collect.forEach(e -> {
                        RentingLedgerExportVO vo = LedgerExportConvert.INSTANCE.convertRenting(f);
                        vo.setAge(getAge(vo.getCustomerIdNumber()));
                        vo.setRoommateName(e.getRoommateName());
                        vo.setRoommatePhone(e.getRoommatePhone());
                        vo.setRoommateIdNumber(e.getRoommateIdNumber());
                        list.add(vo);
                    });
                }else{
                    RentingLedgerExportVO vo = LedgerExportConvert.INSTANCE.convertRenting(f);
                    vo.setAge(getAge(vo.getCustomerIdNumber()));
                    list.add(vo);
                }
            });
            excelWriter.write(list, sheet);
        }
        excelWriter.finish();
        //上传到oos返回URL
        String url = oosFileService.oosUploadFile(new File(filePath));
        ExportRespVO resp = new ExportRespVO();
        if(url.startsWith("http:")){
            url = url.replace("http:","https:");
        }
        resp.setUrl(url);
        resp.setFileName(fileName);
        return resp;
    }


    @Override
    public ExportRespVO renewalLedgerExport(RenewalLedgerReportReqVO req) {
        LoginUser loginUser = SecurityFrameworkUtils.getLoginUser();
        if(loginUser==null){
            throw new ServiceException(ErrorCodeConstants.USER_NOT_LOGIN);
        }
        req.setTenantId(loginUser.getTenantId());
//        req.setCustomerType(CustomTypeEnum.getTypeByValue(req.getCustomerType()));
        ArrayList<String> statusList = Lists.newArrayList(ContractStatusEnum.ACTIVE.getStatus(),ContractStatusEnum.CHECK_IN.getStatus());
        ArrayList<String> nodeList = Lists.newArrayList(ContractNodeEnum.RENEWAL.getNode());
        long count = reportLedgerInfoMapper.countRenewalLedgerForExport(req, statusList, nodeList);
        if (count <= 0) {
            throw new ServiceException(ErrorCodeConstants.RENEWAL_LEDGER_NOT_EXISTS);
        }
        int pageSize = 2000;
        int totalPage = (int) count / pageSize;
        if (count % pageSize != 0) {
            totalPage++;
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmss");
        String format = sdf.format(new Date());
        String fileName = format + "续租台账.xlsx";
        String filePath = configValues.getNfsPath() + File.separator + fileName;
        ExcelWriter excelWriter = EasyExcel.write(filePath, RenewalLedgerExportVO.class).build();
        WriteSheet sheet = EasyExcel.writerSheet("sheet1").build();
        for (int i = 1; i <= totalPage; i++) {
            List<ReportLedgerInfoDO> infoDOList = reportLedgerInfoMapper.selectRenewalLedgerListForExport(req, statusList, nodeList, (i - 1) * pageSize, pageSize);
            List<RenewalLedgerExportVO> list = new ArrayList<>();
            List<Long> contractIds = infoDOList.stream().map(ReportLedgerInfoDO::getContractId).collect(Collectors.toList());
            List<ContractRoommateDO> roommateDOS = contractRoommateMapper.selectByRoomContractIds(contractIds);

            //@date 2023/11/23 shen update
            infoDOList.stream().forEach(f->{
                //当前合同 下同住人
                List<ContractRoommateDO> collect =
                        roommateDOS.stream().filter(rs -> f.getContractId().equals(rs.getRoomContractId())).collect(Collectors.toList());
                if(CollectionUtil.isNotEmpty(collect)){
                    collect.forEach(e -> {
                        RenewalLedgerExportVO vo = LedgerExportConvert.INSTANCE.convertRenewal(f);
                        vo.setOldContractDate(f.getOldStartTime() + "~" + f.getOldEndTime());
                        vo.setRoommateName(e.getRoommateName());
                        vo.setRoommatePhone(e.getRoommatePhone());
                        vo.setRoommateIdNumber(e.getRoommateIdNumber());
                        list.add(vo);
                    });
                }else{
                    RenewalLedgerExportVO vo = LedgerExportConvert.INSTANCE.convertRenewal(f);
                    list.add(vo);
                }
            });

//            roommateDOS.forEach(e -> {
//                ReportLedgerInfoDO infoDO = infoDOList.stream().filter(info -> info.getContractId().equals(e.getRoomContractId())).findFirst().orElse(null);
//                if (infoDO == null) {
//                    return;
//                }
//                RenewalLedgerExportVO vo = LedgerExportConvert.INSTANCE.convertRenewal(infoDO);
//                vo.setOldContractDate(infoDO.getOldStartTime() + "~" + infoDO.getOldEndTime());
//                vo.setRoommateName(e.getRoommateName());
//                vo.setRoommatePhone(e.getRoommatePhone());
//                vo.setRoommateIdNumber(e.getRoommateIdNumber());
//                list.add(vo);
//            });
            excelWriter.write(list, sheet);
        }
        excelWriter.finish();
        //上传到oos返回URL
        String url = oosFileService.oosUploadFile(new File(filePath));
        ExportRespVO resp = new ExportRespVO();
        if(url.startsWith("http:")){
            url = url.replace("http:","https:");
        }
        resp.setUrl(url);
        resp.setFileName(fileName);
        return resp;
    }


    @Override
    public ExportRespVO exchangeLedgerExport(LedgerReportReqVO req) {
        LoginUser loginUser = SecurityFrameworkUtils.getLoginUser();
        if(loginUser==null){
            throw new ServiceException(ErrorCodeConstants.USER_NOT_LOGIN);
        }
        req.setTenantId(loginUser.getTenantId());
//        req.setCustomerType(CustomTypeEnum.getTypeByValue(req.getCustomerType()));
        ArrayList<String> statusList = Lists.newArrayList(ContractStatusEnum.ACTIVE.getStatus(),ContractStatusEnum.CHECK_IN.getStatus());
        ArrayList<String> nodeList = Lists.newArrayList(ContractNodeEnum.EXCHANGE.getNode());
        long count = reportLedgerInfoMapper.countLedgerForExport(req, statusList, nodeList);
        if (count <= 0) {
            throw new ServiceException(ErrorCodeConstants.EXCHANGE_LEDGER_NOT_EXISTS);
        }
        int pageSize = 2000;
        int totalPage = (int) count / pageSize;
        if (count % pageSize != 0) {
            totalPage++;
        }

        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmss");
        String format = sdf.format(new Date());
        String fileName = format + "换租台账.xlsx";

        String filePath = configValues.getNfsPath() + File.separator + fileName;
        ExcelWriter excelWriter = EasyExcel.write(filePath, ExchangeLedgerExportVO.class).build();
        WriteSheet sheet = EasyExcel.writerSheet("sheet1").build();
        for (int i = 1; i <= totalPage; i++) {
            List<ReportLedgerInfoDO> infoDOList = reportLedgerInfoMapper.selectLedgerListForExport(req, statusList, nodeList, (i - 1) * pageSize, pageSize);
            List<ExchangeLedgerExportVO> list = LedgerExportConvert.INSTANCE.covertExchange(infoDOList);
            list.forEach(f-> f.setAge(getAge(f.getCustomerIdNumber())));
            excelWriter.write(list, sheet);
        }
        excelWriter.finish();
        //上传到oos返回URL
        String url = oosFileService.oosUploadFile(new File(filePath));
        ExportRespVO resp = new ExportRespVO();
        if(url.startsWith("http:")){
            url = url.replace("http:","https:");
        }
        resp.setUrl(url);
        resp.setFileName(fileName);
        return resp;
    }


    @Override
    public ExportRespVO rentingOutLedgerExport(LedgerReportReqVO req) {
        LoginUser loginUser = SecurityFrameworkUtils.getLoginUser();
        if(loginUser==null){
            throw new ServiceException(ErrorCodeConstants.USER_NOT_LOGIN);
        }
        req.setTenantId(loginUser.getTenantId());
//        req.setCustomerType(CustomTypeEnum.getTypeByValue(req.getCustomerType()));
        ArrayList<String> statusList = Lists.newArrayList(ContractStatusEnum.NORMAL_END.getStatus(), ContractStatusEnum.ABNORMAL_END.getStatus());
        ArrayList<String> nodeList = Lists.newArrayList(ContractNodeEnum.RENTING_OUT.getNode());
        req.setIsRentalOutLedger("Y");
        long count = reportLedgerInfoMapper.countLedgerForExport(req, statusList, nodeList);
        if (count <= 0) {
            throw new ServiceException(ErrorCodeConstants.RENTING_OUT_LEDGER_NOT_EXISTS);
        }
        int pageSize = 2000;
        int totalPage = (int) count / pageSize;
        if (count % pageSize != 0) {
            totalPage++;
        }

        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmss");
        String format = sdf.format(new Date());
        String fileName = format + "退租台账.xlsx";

        String filePath = configValues.getNfsPath() + File.separator + fileName;
        ExcelWriter excelWriter = EasyExcel.write(filePath, RentingOutLedgerExportVO.class).build();
        WriteSheet sheet = EasyExcel.writerSheet("sheet1").build();
        for (int i = 1; i <= totalPage; i++) {
            List<ReportLedgerInfoDO> infoDOList = reportLedgerInfoMapper.selectLedgerListForExport(req, statusList, nodeList, (i - 1) * pageSize, pageSize);
            List<RentingOutLedgerExportVO> list = new ArrayList<>();
            List<Long> contractIds = infoDOList.stream().map(ReportLedgerInfoDO::getContractId).collect(Collectors.toList());
            List<ContractRoommateDO> roommateDOS = contractRoommateMapper.selectByRoomContractIds(contractIds);

            //@date 2023/11/23 shen update
            infoDOList.stream().forEach(f->{
                //当前合同 下同住人
                List<ContractRoommateDO> collect =
                        roommateDOS.stream().filter(rs -> f.getContractId().equals(rs.getRoomContractId())).collect(Collectors.toList());
                if(CollectionUtil.isNotEmpty(collect)){
                    collect.forEach(e -> {
                        RentingOutLedgerExportVO vo = LedgerExportConvert.INSTANCE.convertRentingOut(f);
                        vo.setAge(getAge(vo.getCustomerIdNumber()));
                        vo.setRoommateName(e.getRoommateName());
                        vo.setRoommatePhone(e.getRoommatePhone());
                        vo.setRoommateIdNumber(e.getRoommateIdNumber());
                        list.add(vo);
                    });
                }else{
                    RentingOutLedgerExportVO vo = LedgerExportConvert.INSTANCE.convertRentingOut(f);
                    vo.setAge(getAge(vo.getCustomerIdNumber()));
                    list.add(vo);
                }
            });

           /* roommateDOS.forEach(e -> {
                ReportLedgerInfoDO infoDO = infoDOList.stream().filter(info -> info.getContractId().equals(e.getRoomContractId())).findFirst().orElse(null);
                if (infoDO == null) {
                    return;
                }
                RentingOutLedgerExportVO vo = LedgerExportConvert.INSTANCE.convertRentingOut(infoDO);
                vo.setAge(getAge(vo.getCustomerIdNumber()));
                vo.setRoommateName(e.getRoommateName());
                vo.setRoommatePhone(e.getRoommatePhone());
                vo.setRoommateIdNumber(e.getRoommateIdNumber());
                list.add(vo);
            });*/
            excelWriter.write(list, sheet);
        }
        excelWriter.finish();
        //上传到oos返回URL
        String url = oosFileService.oosUploadFile(new File(filePath));
        ExportRespVO resp = new ExportRespVO();
        if(url.startsWith("http:")){
            url = url.replace("http:","https:");
        }
        resp.setUrl(url);
        resp.setFileName(fileName);
        return resp;
    }

    private LambdaQueryWrapper<ReportLedgerInfoDO> getQueryWrapper(LedgerReportReqVO reqVO) {
        LambdaQueryWrapper<ReportLedgerInfoDO> queryWrapper = new LambdaQueryWrapper<>();
        if (!ObjectUtils.isEmpty(reqVO.getApartmentId())) {
            queryWrapper.eq(ReportLedgerInfoDO::getApartmentId, reqVO.getApartmentId());
        }
        if (StringUtils.isNotBlank(reqVO.getCustomerType())) {
            queryWrapper.eq(ReportLedgerInfoDO::getCustomerType,reqVO.getCustomerType());
        }
        if (StringUtils.isNotBlank(reqVO.getArea())) {
            queryWrapper.like(ReportLedgerInfoDO::getArea,reqVO.getArea());
        }
        if (!ObjectUtils.isEmpty(reqVO.getSignedStartDate()) && !ObjectUtils.isEmpty(reqVO.getSignedEndDate())) {
            queryWrapper.ge(ReportLedgerInfoDO::getSignedTime, reqVO.getSignedStartDate());
            queryWrapper.le(ReportLedgerInfoDO::getSignedTime, reqVO.getSignedEndDate());
        }
        if (!ObjectUtils.isEmpty(reqVO.getContractStartDateEnd()) && !ObjectUtils.isEmpty(reqVO.getContractStartDateFrom())) {
            queryWrapper.ge(ReportLedgerInfoDO::getStartTime, reqVO.getContractStartDateFrom());
            queryWrapper.le(ReportLedgerInfoDO::getStartTime, reqVO.getContractStartDateEnd());
        }
        if (!ObjectUtils.isEmpty(reqVO.getContractEndDateFrom()) && !ObjectUtils.isEmpty(reqVO.getContractEndDateEnd())) {
            queryWrapper.ge(ReportLedgerInfoDO::getEndTime, reqVO.getContractEndDateFrom());
            queryWrapper.le(ReportLedgerInfoDO::getEndTime, reqVO.getContractEndDateEnd());
        }
        if (StringUtils.isNotBlank(reqVO.getMixQueryParam())) {
            queryWrapper.and(wrapper->  wrapper.like(ReportLedgerInfoDO::getCustomerName, reqVO.getMixQueryParam()).or()
                    .like(ReportLedgerInfoDO::getCustomerPhone, reqVO.getMixQueryParam()).or()
                    .like(ReportLedgerInfoDO::getHouseResource, reqVO.getMixQueryParam()  ));
        }
        queryWrapper.eq(ReportLedgerInfoDO::getHandleFlag,1);
        return queryWrapper;
    }
    private LambdaQueryWrapper<ReportLedgerInfoDO> getRenewalQueryWrapper(RenewalLedgerReportReqVO reqVO) {
        LambdaQueryWrapper<ReportLedgerInfoDO> queryWrapper = new LambdaQueryWrapper<>();
        if (!ObjectUtils.isEmpty(reqVO.getApartmentId())) {
            queryWrapper.eq(ReportLedgerInfoDO::getApartmentId, reqVO.getApartmentId());
        }
        if (StringUtils.isNotBlank(reqVO.getCustomerType())) {
            queryWrapper.eq(ReportLedgerInfoDO::getCustomerType,reqVO.getCustomerType());
        }
        if (StringUtils.isNotBlank(reqVO.getArea())) {
            queryWrapper.like(ReportLedgerInfoDO::getArea,reqVO.getArea());
        }
        if (!ObjectUtils.isEmpty(reqVO.getNewContractDateE()) && !ObjectUtils.isEmpty(reqVO.getNewContractDateS())) {
            queryWrapper.ge(ReportLedgerInfoDO::getNewContractDate, reqVO.getNewContractDateS());
            queryWrapper.le(ReportLedgerInfoDO::getNewContractDate, reqVO.getNewContractDateE());
        }
        if (!ObjectUtils.isEmpty(reqVO.getContractStartDateEnd()) && !ObjectUtils.isEmpty(reqVO.getContractStartDateFrom())) {
            queryWrapper.ge(ReportLedgerInfoDO::getStartTime, reqVO.getContractStartDateFrom());
            queryWrapper.le(ReportLedgerInfoDO::getStartTime, reqVO.getContractStartDateEnd());
        }
        if (!ObjectUtils.isEmpty(reqVO.getContractEndDateFrom()) && !ObjectUtils.isEmpty(reqVO.getContractEndDateEnd())) {
            queryWrapper.ge(ReportLedgerInfoDO::getEndTime, reqVO.getContractEndDateFrom());
            queryWrapper.le(ReportLedgerInfoDO::getEndTime, reqVO.getContractEndDateEnd());
        }
        if (StringUtils.isNotBlank(reqVO.getMixQueryParam())) {
            queryWrapper.and(wrapper->  wrapper.like(ReportLedgerInfoDO::getCustomerName, reqVO.getMixQueryParam()).or()
                    .like(ReportLedgerInfoDO::getCustomerPhone, reqVO.getMixQueryParam()).or()
                    .like(ReportLedgerInfoDO::getHouseResource, reqVO.getMixQueryParam()  ));
        }
        queryWrapper.eq(ReportLedgerInfoDO::getHandleFlag,1);
        return queryWrapper;
    }

}
